﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Transactions;
using AB.ServiceDirectory.Commons.DataFilters;
using AB.ServiceDirectory.Entities;

namespace AB.ServiceDirectory.Dao
{
    public class ServiceDao
    {
        private readonly ServicesDirectoryDataContext _context = DB.Context;

        /// <summary>
        /// Gets the gov office regions.
        /// </summary>
        /// <param name="filter">The filter.</param>
        /// <param name="sortType">Type of the sort.</param>
        /// <param name="startRowIndex">Start index of the row.</param>
        /// <param name="max">The max.</param>
        /// <returns>
        /// GovOfficeRegion list
        /// </returns>
        public IQueryable<Service> GetList(ServiceFilter filter, string sortType, int startRowIndex, int max)
        {
            var data = from gor in _context.Services
                       select gor;

            // 1. Filter by condition
            if (filter != null)
            {
                data = GetListQuery(data, filter);
            }

            // 2. Sorting
            data = GetListSort(data, sortType);

            // 3. Limit the rows
            data = data.Skip(startRowIndex).Take(max);

            return data;
        }

        /// <summary>
        /// Gets the list sort.
        /// </summary>
        /// <param name="data">The data.</param>
        /// <param name="sortType">Type of the sort.</param>
        /// <returns></returns>
        private static IQueryable<Service> GetListSort(IQueryable<Service> data, string sortType)
        {
            var sortDescending = false;
            if (!string.IsNullOrEmpty(sortType))
            {
                var values = sortType.Split(' ');
                sortType = values[0];

                if (values.Length > 1)
                {
                    sortDescending = (values[1] == "DESC");
                }

                switch (sortType)
                {
                    case "ServiceName":
                        data = sortDescending
                                   ? data.OrderByDescending(g => g.ServiceName)
                                   : data.OrderBy(g => g.ServiceName);
                        break;

                    case "Description":
                        data = sortDescending
                                   ? data.OrderByDescending(g => g.ShortDescription)
                                   : data.OrderBy(g => g.ShortDescription);
                        break;

                    case "ContactName":
                        data = sortDescending
                                   ? data.OrderByDescending(g => g.Contact.FirstName).ThenBy(g => g.Contact.Surname)
                                   : data.OrderBy(g => g.Contact.FirstName).ThenBy(g => g.Contact.Surname);
                        break;

                    case "IsActive":
                        data = sortDescending
                                   ? data.OrderByDescending(g => g.IsActive)
                                   : data.OrderBy(g => g.IsActive);
                        break;
                    default:
                        data = sortDescending
                                   ? data.OrderByDescending(g => g.ServiceId)
                                   : data.OrderBy(g => g.ServiceId);
                        break;
                }
            }

            return data;
        }

        /// <summary>
        /// Gets the list query.
        /// </summary>
        /// <param name="data">The data.</param>
        /// <param name="filter">The filter.</param>
        /// <returns></returns>
        private static IQueryable<Service> GetListQuery(IQueryable<Service> data, ServiceFilter filter)
        {
            // Filter by beginning of the letters
            if (filter.BeginningLetters != null)
            {
                data = data.Where(g => filter.BeginningLetters.Contains(g.ServiceName[0].ToString()));
            }

            // Filter by in-active & active Gor
            if (!filter.IncludeInactive)
            {
                data = data.Where(g => g.IsActive == !filter.IncludeInactive);
            }

            return data;
        }

        /// <summary>
        /// Gets the list count.
        /// </summary>
        /// <param name="filter">The filter.</param>
        /// <returns></returns>
        public int GetListCount(ServiceFilter filter)
        {
            var data = from gor in _context.Services
                       select gor;

            if (filter != null)
            {
                data = GetListQuery(data, filter);
            }
            return data.Count();
        }

        /// <summary>
        /// Gets the gov office region by id.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns>GovOfficeRegion object</returns>
        public Service GetServiceById(int id)
        {
            var service = _context.Services.FirstOrDefault(g => g.ServiceId == id);
            return service;
        }

        /// <summary>
        /// Sets the service status.
        /// </summary>
        /// <param name="serviceId">The service id.</param>
        /// <param name="status">if set to <c>true</c> [status].</param>
        public bool SetServiceStatus(int serviceId, bool status)
        {
            var service = _context.Services.FirstOrDefault(s => s.ServiceId == serviceId);
            if (service != null)
            {
                service.IsActive = status;
                _context.SubmitChanges();
                return true;
            }

            return false;
        }

        /// <summary>
        /// Gets the service reference data by category id.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        /// <returns></returns>
        public IQueryable<ServiceReferenceData> GetServiceReferenceDataByCategoryId(int categoryId)
        {
            var serviceReferenceData = from sr in _context.ServiceReferenceDatas
                                       where sr.CategoryId == categoryId
                                       select sr;
            return serviceReferenceData;
        }

        /// <summary>
        /// Gets the service reference by id.
        /// </summary>
        /// <param name="serviceId">The service id.</param>
        /// <returns></returns>
        public ServiceReference GetServiceReferenceById(int serviceId)
        {
            var serviceReference = _context.ServiceReferences.FirstOrDefault(s => s.ServiceId == serviceId);
            return serviceReference;
        }

        /// <summary>
        /// Gets the intervention for service.
        /// </summary>
        /// <param name="serviceId">The service id.</param>
        /// <returns></returns>
        public IQueryable<Intervention> GetInterventionForService(int serviceId)
        {
            var interventions = from i in _context.Interventions
                                where (i.IsActive && i.ServiceId == serviceId) || i.ServiceId == null
                                select i;
            return interventions;
        }

        /// <summary>
        /// Gets the other active service.
        /// </summary>
        /// <param name="serviceId">The service id.</param>
        /// <returns></returns>
        public IQueryable<Service> GetOtherActiveService(int serviceId)
        {
            Func<Service, Service> make = h => new Service {ServiceId = h.ServiceId, ServiceName = h.ServiceName};
            var services = from s in _context.Services
                           where s.ServiceId != serviceId && s.IsActive
                           select make(s);
            return services;
        }

        /// <summary>
        /// Adds the service.
        /// </summary>
        /// <param name="service">The service.</param>
        /// <returns></returns>
        public bool AddService(Service service)
        {
            var status = false;
            try
            {
                _context.Services.InsertOnSubmit(service);
                _context.SubmitChanges();
                status = true;
            }
            catch (SqlException ex)
            {
                Log.GetLog(this).Error(ex);
                status = false;
            }

            return status;
        }

        /// <summary>
        /// Edits the intervention list.
        /// </summary>
        /// <param name="interventions">The interventions.</param>
        /// <returns></returns>
        public bool EditInterventionList(ICollection<Intervention> interventions)
        {
            using (var trans = new TransactionScope())
            {
                foreach (var item in interventions)
                {
                    var intervention =
                        _context.Interventions.FirstOrDefault(i => i.InterventionId == item.InterventionId);
                    if (intervention != null)
                    {
                        intervention.ServiceId = item.ServiceId;
                    }
                }

                _context.SubmitChanges();
                trans.Complete();
                return true;
            }
        }

        /// <summary>
        /// Edits the service.
        /// </summary>
        /// <param name="service">The service.</param>
        /// <returns></returns>
        public bool EditService(Service service)
        {
            var status = false;
            try
            {
                // Service information
                var editedService = GetServiceById(service.ServiceId);
                editedService.ServiceName = service.ServiceName;
                editedService.ShortDescription = service.ShortDescription;
                editedService.FullDescription = service.FullDescription;
                editedService.SubType = service.SubType;
                editedService.DeptCode = service.DeptCode;
                editedService.ServiceType = service.ServiceType;
                editedService.ClientDescription = service.ClientDescription;
                editedService.DescriptionDelivery = service.DescriptionDelivery;
                editedService.Attendance = service.Attendance;
                editedService.ContractCode = service.ContractCode;
                editedService.ContractValue = service.ContractValue;
                editedService.StartExpected = service.StartExpected;
                editedService.StartDate = service.StartDate;
                editedService.EndDate = service.EndDate;
                editedService.ContractStagedPayment = service.ContractStagedPayment;
                editedService.ReferrealProcess = service.ReferrealProcess;
                editedService.ExtendableYears = service.ExtendableYears;
                editedService.ExtendableMonths = service.ExtendableMonths;
                editedService.LimitedYears = service.LimitedYears;
                editedService.LimitedMonths = service.LimitedMonths;
                if (editedService.ContactId != service.ContactId)
                {
                    var newContact = HungContactDao.SelectContactById(service.ContactId);
                    editedService.Contact = newContact;
                }

                // Service reference information
                var editedServiceRef = editedService.ServiceReference;
                var serviceRef = service.ServiceReference;
                editedServiceRef.BenefitsCriterion = serviceRef.BenefitsCriterion;
                editedServiceRef.BarriersCriterion = serviceRef.BarriersCriterion;
                editedServiceRef.EthnicityCriterion = serviceRef.EthnicityCriterion;
                editedServiceRef.DisabilityCriterion = serviceRef.DisabilityCriterion;
                editedServiceRef.PersonalCircumstancesCriterion = serviceRef.PersonalCircumstancesCriterion;
                editedServiceRef.ParticipationCriterion = serviceRef.ParticipationCriterion;

                editedServiceRef.ClientSupportProcess = serviceRef.ClientSupportProcess;
                editedServiceRef.ClientOutcome = serviceRef.ClientOutcome;
                editedServiceRef.TargetClient = serviceRef.TargetClient;
                editedServiceRef.ClientJourney = serviceRef.ClientJourney;
                editedServiceRef.Accreditations = serviceRef.Accreditations;
                editedServiceRef.ReferralSources = serviceRef.ReferralSources;
                editedServiceRef.SupportCentres = serviceRef.SupportCentres;
                editedServiceRef.ProgramId = serviceRef.ProgramId;

                if (service.SubType.Equals("Funding"))
                {
                    // Service funding information
                    var editedFunding = editedService.ServiceFunding;
                    if (editedFunding != null)
                    {
                        var funding = service.ServiceFunding;
                        editedFunding.Contact = HungContactDao.SelectContactById(editedFunding.FundingContactId);
                        editedFunding.FundingSource = funding.FundingSource;
                        editedFunding.FundraisingForText = funding.FundraisingForText;
                        editedFunding.FundraisingWhy = funding.FundraisingWhy;
                        editedFunding.FundraisingDonationIncremental = funding.FundraisingDonationIncremental;
                        editedFunding.FundraisingDonorAnonymous = funding.FundraisingDonorAnonymous;
                        editedFunding.FundingContinuationDetails = funding.FundingContinuationDetails;
                        editedFunding.FundingContinuationAmount = funding.FundingContinuationAmount;

                        editedFunding.FundingAmount = funding.FundingAmount;
                        editedFunding.FundingNeeds = funding.FundingNeeds;
                        editedFunding.FundraisingNeeds = funding.FundraisingNeeds;
                        editedFunding.FundraisingDonorAmount = funding.FundraisingDonorAmount;

                        editedFunding.FundingStart = funding.FundingStart;
                        editedFunding.FundingEnd = funding.FundingEnd;
                        editedFunding.FundraisingRequiredBy = funding.FundraisingRequiredBy;
                        editedFunding.FundraisingDonationDate = funding.FundraisingDonationDate;
                        editedFunding.FundraisingCompletedDate = funding.FundraisingCompletedDate;
                    }
                    else
                    {
                        editedService.ServiceFunding = service.ServiceFunding;
                    }
                }
                else if (service.SubType.Equals("Contract"))
                {
                    // Service contract information
                    var editedContract = editedService.ServiceContract;
                    if (editedContract != null)
                    {
                        var contract = service.ServiceContract;
                        editedContract.ContractOutcome = contract.ContractOutcome;
                        editedContract.ContractObligation = contract.ContractObligation;
                        editedContract.Participation = contract.Participation;
                    }
                    else
                    {
                        editedService.ServiceContract = service.ServiceContract;
                    }
                }

                _context.SubmitChanges();
                status = true;
            }
            catch (SqlException ex)
            {
                Log.GetLog(this).Error(ex);
                status = false;
            }

            return status;
        }

        /// <summary>
        /// Adds the internal service.
        /// </summary>
        /// <param name="list">The list.</param>
        /// <returns></returns>
        public bool AddInternalService(IEnumerable<InternalService> list)
        {
            try
            {
                using (var trans = new TransactionScope())
                {
                    _context.InternalServices.InsertAllOnSubmit(list);
                    _context.SubmitChanges();
                    trans.Complete();
                    return true;
                }
            }
            catch (SqlException ex)
            {
                Log.GetLog(this).Error(ex);
                return false;
            }
        }

        public bool EditInternalService(int serviceId, ICollection<InternalService> list)
        {
            try
            {
                using (var trans = new TransactionScope())
                {
                    var editedList = from op in _context.InternalServices
                                     where op.ServiceId == serviceId
                                     select op;

                    InternalService objOp = null;
                    foreach (var program in editedList)
                    {
                        objOp = list.FirstOrDefault(op => op.OtherServiceId == program.OtherServiceId);
                        if (objOp == null)
                        {
                            _context.InternalServices.DeleteOnSubmit(program);
                            _context.SubmitChanges();
                            continue;
                        }

                        list.Remove(objOp);
                    }

                    _context.InternalServices.InsertAllOnSubmit(list);
                    _context.SubmitChanges();
                    trans.Complete();
                }
            }
            catch (SqlException ex)
            {
                Log.GetLog(this).Error(ex);
                return false;
            }
            return false;
        }

        public IQueryable<InternalService> GetInternalService(int serviceId)
        {
            var internalServices = _context.InternalServices.Where(s => s.ServiceId == serviceId);
            return internalServices;
        }

        public bool IsNameExists(string serviceName)
        {
            var service = _context.Services.FirstOrDefault(s => s.ServiceName.Equals(serviceName));
            return service != null;
        }
    }
}